﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace TopModel
{
    public class ProcHelper
    {
        private SqlConnection m_conn;
        private SqlCommand m_cmd;

        public ProcHelper()
        {
            m_conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString);
            m_cmd = new SqlCommand();
            m_cmd.CommandType = CommandType.StoredProcedure;
            m_cmd.Connection = m_conn;
        }

        public ProcHelper(string connstr)
        {
            m_conn = new SqlConnection(connstr);
            m_cmd = new SqlCommand();
            m_cmd.CommandType = CommandType.StoredProcedure;
            m_cmd.Connection = m_conn;
        }

        public void AddParameters(string parameterName, SqlDbType type, int size, object value)
        {
            SqlParameter parameter = new SqlParameter() { ParameterName = parameterName, Value = value, SqlDbType = type, Size = size };
            m_cmd.Parameters.Add(parameter);
        }
        public void AddParameters(string parameterName, SqlDbType type, object value)
        {
            SqlParameter parameter = new SqlParameter() { ParameterName = parameterName, SqlDbType = type, Value = value };
            m_cmd.Parameters.Add(parameter);
        }


        public void AddParameters(string parameterName, SqlDbType type, ParameterDirection direction,object value)
        {
            SqlParameter parameter = new SqlParameter() { ParameterName = parameterName, SqlDbType = type, Value = value, Direction = direction };
            m_cmd.Parameters.Add(parameter);
        }
        public void AddParameters(string parameterName, SqlDbType type, int size, ParameterDirection direction, object value)
        {
            SqlParameter parameter = new SqlParameter() { ParameterName = parameterName, SqlDbType = type, Size = size, Value = value, Direction = direction };
            m_cmd.Parameters.Add(parameter);
        }
        public void ClearParameters()
        {
            m_cmd.Parameters.Clear();
        }

        public void ExecuteNonQuery(string procName)
        {
            try
            {
                m_cmd.Connection.Open();
                m_cmd.CommandText = procName;
                m_cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("ExecuteNonQuery", e);
            }
            finally
            {
                m_cmd.Connection.Close();
            }
        }

        public DataTable GetDataTable(string procName)
        {
            DataTable dt = new DataTable();
            try
            {
                m_cmd.Connection.Open();
                m_cmd.CommandText = procName;
                SqlDataAdapter da = new SqlDataAdapter(m_cmd);
                da.Fill(dt);
            }
            catch (Exception e)
            {
                throw new Exception("GetDataTable", e);
            }
            finally
            {
                m_cmd.Connection.Close();
            }
            return dt;
        }

        public object GetReturnValue(string paramName)
        {
            return m_cmd.Parameters[paramName].Value;
        }
    }
}
